//解析excel文件的服务
const ExcelJS = require('exceljs');

//取得第一个不隐藏的sheet并解析数据,可传递表头行号和数据开始行的行号
async function parseExcelFileFirstSheet(filePath,nameRowNumber=1,firstDataRowNumber=2){
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(filePath);
    //只看没隐藏的表
    const visibleWorksheets=workbook.worksheets.filter(s=>'visible'===s.state);
    //取得第一张表
    let worksheet = visibleWorksheets[0];
    return parseSheet(worksheet,nameRowNumber,firstDataRowNumber);
}

//解析一张sheet,可传递表头行号和数据开始行的行号,
function parseSheet(worksheet,nameRowNumber=1,firstDataRowNumber=2){
    //console.log(worksheet.name);
    const namesRow=worksheet.getRow(nameRowNumber);
    const nameIndexMapping={};//列号与列名的对应
    // 连续遍历所有非空单元格
    namesRow.eachCell(function(cell, colNumber) {
        nameIndexMapping[colNumber]=cell.value;
    });
    const sheetData=[];
    for(let i=firstDataRowNumber;i<=worksheet.lastRow.number;i++){
        const row=worksheet.getRow(i);
        const rowData={};
        row.eachCell(function(cell, colNumber) {
            const fieldName=nameIndexMapping[colNumber];
            const value = cell.value;
            if(value){
                const type = typeof(value);
                if(type==='string'){
                    if(value.trim().length>0){
                        rowData[fieldName]=value.trim();
                    }
                }else if(type==='number'){
                    var sValue;
                    try{
                        sValue = value.toString();
                        rowData[fieldName]=sValue;
                    }catch(e){
                       console.log('excel/parseSheet 类型为number 读取第'+i+'行的'+fieldName+'列值：'+value+'转字符串出错，'+'原因:'+e.message);
                    }
                }else if(type==='object'){
                    var sValue;
                    try{
                        if(value&&value.richText){
                            const arr=value.richText.map(r=>r.text);
                            sValue=arr.join('')
                        }
                        rowData[fieldName]=sValue;
                    }catch(e){
                        console.log('excel/parseSheet 数据类型为'+type+' 读取第'+i+'行的'+fieldName+'列值：'+value+'转字符串出错，'+'原因:'+e.message);
                    }
                }else{
                    var sValue;
                    try{
                        sValue = value.toString();
                        rowData[fieldName]=sValue;
                    }catch(e){
                        console.log('excel/parseSheet 类型为number 读取第'+i+'行的'+fieldName+'列值：'+value+'转字符串出错，'+'原因:'+e.message);
                    }
                }
            }
        });
        if(Object.keys(rowData).length>0){
            sheetData.push(rowData);
        }
    }
    return sheetData;
}

//每个sheet对应一个名单，sheet名为名单名
//第一列为username,第一行是字段名
//返回举例：{ Sheet1: [ '111', '222' ], Sheet2: [ 'aa', 'bb', 'cc' ] }
async function parseExcelFileNameLists(filePath,dataColumnNumber=1,firstDataRowNumber=2){
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(filePath);
    //只看没隐藏的表
    const visibleWorksheets=workbook.worksheets.filter(s=>'visible'===s.state);
    const nameListDict={};
    visibleWorksheets.forEach(worksheet=>{
        nameListDict[worksheet.name]=parseSheet1Column(worksheet,dataColumnNumber,firstDataRowNumber)
    });
    return nameListDict;
}

//解析一张sheet的1列数据,返回为1个数组。可传递数据所在列和数据开始行的行号,
//返回样例： [ 'aa', 'bb', 'cc' ]
function parseSheet1Column(worksheet,dataColumnNumber=1,firstDataRowNumber=2){
    console.log(worksheet.name);
    const results=[];
    const dataColumn=worksheet.getColumn(dataColumnNumber);
    dataColumn.eachCell((cell,rowNumber)=>{
        if(rowNumber>=firstDataRowNumber && cell.value){
            results.push(String(cell.value));
        }
    });
    return results;
}
module.exports={
    parseExcelFileFirstSheet,
    parseExcelFileNameLists
};